from model.MysqlPool import pool
from tornado import gen


class Course:
    # ----------add----------
    @classmethod
    @gen.coroutine
    def add(cls, c_name, c_date, c_number, t_id, c_day, c_place, start_week, end_week, start_date, c_id) -> bool:
        """
        添加课程
        :param c_name: 课程名
        :param c_date: 上课日期
        :param c_number: 节次
        :param c_id: 课程id
        :return:
        """
        sql = "INSERT INTO course_info " \
              "(c_id,c_number,c_date,c_name,t_id,c_day,c_place,start_week,end_week,start_date) " \
              "VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        parm = (c_id, c_number, c_date, c_name, t_id, c_day, c_place, start_week, end_week, start_date)
        cursor = yield pool.execute(sql, parm)
        return cursor.rowcount != 0

    @classmethod
    @gen.coroutine
    def add_bind_to_cid_for_student(cls, s_id, c_id):
        """
        绑定学生和课程号
        :param s_id:
        :param c_id:
        :return:
        """
        sql = "select count(c_id) as count from course_info where c_id=%s"
        cursor = yield pool.execute(sql, (c_id, ))
        if cursor.fetchone()['count'] == 0:
            return False

        sql = "insert into s_c(s_id, c_id) VALUES (%s,%s)"
        cursor = yield pool.execute(sql, (s_id, c_id))
        return cursor.rowcount != 0

    # ----------get----------
    @classmethod
    @gen.coroutine
    def get_max_c_id(cls):
        """
        返回最大cid, 进程不安全!
        :return:
        """
        sql = "select max(c_id) as max from course_info"
        cursor = yield pool.execute(sql)
        return cursor.fetchone()['max']

    @classmethod
    @gen.coroutine
    def get_by_sid(cls, sid):
        """
        通过学号返回课程
        :param sid: 学号
        :return:
        """
        sql = "select * from student_course where id=%s"
        cursor = yield pool.execute(sql, (sid,))
        data = cursor.fetchall()
        for x in data:
            x['start_end'] = "第{}周->第{}周".format(x['start_week'], x['end_week'])
            x['start_date'] = str(x['start_date'])
        return data

    @classmethod
    @gen.coroutine
    def get_by_tid(cls, tid):
        """
        通过职工号返回课程
        :param tid: 职工号
        :return:
        """
        sql = "select * from teacher_course where tid=%s"
        cursor = yield pool.execute(sql, (tid,))
        data = cursor.fetchall()
        for x in data:
            x['start_end'] = "第{}周->第{}周".format(x['start_week'], x['end_week'])
            x['start_date'] = str(x['start_date'])
        return data

    # ----------del----------
    @classmethod
    @gen.coroutine
    def del_by_cid(cls, c_id) -> bool:
        """
        通过课程号删除
        :param c_id: 职工号
        :return:
        """
        sql = "delete from course_info where c_id=%s"
        cursor = yield pool.execute(sql, (c_id,))
        return cursor.rowcount != 0
